package com.question.dao.daoImpl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.question.dao.StudentDao;
import com.question.dao.daoFactory.DaoFactory;
import com.question.domain.Student;
import com.question.utils.MyProperties;

public class StudentDaoImpl implements StudentDao {
	private QueryRunner qr = new TxQueryRunner();
	
	MyProperties properties = new MyProperties();
	
	//用于纪录排名表的人数
	private int num = 0;
	
	public StudentDaoImpl() {
		try {
			num = Integer.valueOf(properties.getProperties("RankStudent"));
		} catch (NumberFormatException | IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public Student findUserById(Student studentId) {
		try {
			String sql = "SELECT * FROM topstudent WHERE studentId = ?";
			Student stu = qr.query(sql, new BeanHandler<Student>(Student.class),studentId.getStudentId());
			if(null==stu) {
				sql = "select * from student where studentId = ?";
				stu = qr.query(sql, new BeanHandler<Student>(Student.class),studentId.getStudentId());
			}
			return stu;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void insertStu(Student student) {
			try {
				System.out.println("dao:"+student.toString());
				String sql = "INSERT INTO student (studentId,userName,academy) VALUES (?,?,?)";
				String[] params = {student.getStudentId().toString(),student.getUserName(),student.getAcademy()};
				qr.update(sql, params);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
	}

	@Override
	public int getRank(Student studentId) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int sRank = 0;
		try {
			con = JdbcUtils.getConnection();
			synchronized (this) {
				//计算该同学所在的分数段
				int m = studentId.getScoure()*2/10;
				//比较排名的依据
				int n = studentId.getScoure()*10000+studentId.getTime();
				//获取此同学在该分数段的排名
				String sql = "SELECT COUNT(*) sum FROM student WHERE (scoure*10000-time)>=? AND (scoure*2/10)=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, n);
				pstmt.setInt(2, m);
				rs = pstmt.executeQuery();
				if(rs==null)
					sRank = 1;
				if(rs.next())sRank = rs.getInt("sum")+1;
				//获取该同学所在分数段前方还有多少人,并加到该同学在其分数段的排名：为该同学的总排名
				sql = "SELECT studentsNumber FROM segment WHERE num>?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, m);
				rs = pstmt.executeQuery();
				while(rs.next()){
					sRank += rs.getInt("studentsNumber");
				}
			}
			return sRank;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
	}

	/**
	 * 将student表中学生的student的信息补全，说明：
	 * 表中的信息中的排名为该分数段的人数
	 * 
	 * @param student
	 */
	@Override
	public void completeStudent(Student student){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = JdbcUtils.getConnection();
			con.setAutoCommit(false);
			int m = student.getScoure()*2/10;
			synchronized (this) {
				//将该同学的信息补全student中的信息，其中student中的排名只是一个形式，没什么作用。
				String sql = "UPDATE student SET scoure=?,time=? WHERE studentId=?";
				pstmt = con.prepareStatement(sql);
				//为pstmt赋值
				pstmt.setInt(1, student.getScoure());
				pstmt.setInt(2, student.getTime());
				pstmt.setInt(3, student.getStudentId());
				//执行
				pstmt.executeUpdate();
				
				//获取原该分数段的人数
				int num_d = 0;
				sql = "SELECT * FROM segment WHERE num=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, m);
				rs = pstmt.executeQuery();
				if(rs==null);
				if(rs.next())num_d = rs.getInt("studentsNumber");
				
				//将该分数段的同学的人数加一
				num_d++;//现在该分数段的人数
				sql = "UPDATE segment SET studentsNumber=? WHERE num = ?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, num_d);
				pstmt.setInt(2, m);
				pstmt.executeUpdate();
				con.commit();
			}
		} catch (Exception e) {
			//回滚事务
			if(con != null) {
				try {
					con.rollback();
				} catch(SQLException ex) {}
			}
			throw new RuntimeException(e);
		}finally{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
	}
	
	@Override
	public void updateRank(Student studentid,int sRank){
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = JdbcUtils.getConnection();
			con.setAutoCommit(false);
			synchronized (this) {
				studentid.setRank(sRank);
				if(sRank>=100)
					return ;
				String sql = "INSERT INTO topstudent (studentId,userName,academy,scoure,rank,time) VALUES (?,?,?,?,?,?)";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, studentid.getStudentId());
				pstmt.setString(2, studentid.getUserName());
				pstmt.setString(3, studentid.getAcademy());
				pstmt.setInt(4, studentid.getScoure());
				pstmt.setInt(5, sRank);
				pstmt.setInt(6, studentid.getTime());
				pstmt.executeUpdate();
				sql = "UPDATE topstudent SET rank = rank+1 WHERE rank>=? AND studentId<>?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, sRank);
				pstmt.setInt(2, studentid.getStudentId());
				pstmt.executeUpdate();
				if(num>=50){
					sql = "DELETE FROM topstudent WHERE rank = ?";
					pstmt = con.prepareStatement(sql);
					pstmt.setInt(1, sRank);
					pstmt.executeUpdate();
					num--;
				}
				num++;	
			}
			con.commit();
		} catch (Exception e) {
			//回滚事务
			if(con != null) {
				try {
					con.rollback();
				} catch(SQLException ex) {}
			}
			throw new RuntimeException(e);
		}finally{
			try {
				if(pstmt!=null)
					pstmt.close();
				if(con!=null)
					con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
	}
	
	@Override
	public List getRanks() {
		//ArrayList<Student> list = new ArrayList<Student>();
		try {
			//要查询的人数
			//int num = Integer.valueOf(properties.getProperties("RankStudent"));
			
			String sql = "SELECT * FROM topstudent WHERE rank<? ORDER BY rank DESC";
			return qr.query(sql, new BeanListHandler<Student>(Student.class), 4);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}


	
	public static void main(String[] args) {
		
		
		StudentDao studentDao = DaoFactory.getInstance().getStudentDao();
		Student stu =new Student();
		//studentId,userName,academy

		System.out.println("ok");
	}
		
	
}
